<!DOCTYPE html>
<html lang="en">
<head>
    <title>怪兽营</title>
    <meta charset="UTF-8"/>
    <meta name="author" content="zqtao|怪兽营">
    <meta name="email" content="zqtao23@163.com">
    <meta name="keywords" content="囧么肥事,怪兽营,怪兽,机器学习,python,Monster,Ubuntu20.04, Ubuntu20.04安装mysql8.0并设置远程连接"/>
    <meta name="description" content="怪兽营,快乐的咆哮怪兽, Ubuntu20.04, Ubuntu20.04安装mysql8.0并设置远程连接"/>
    <meta name="viewport"
          content="width=device-width,height=device-height,initial-scale=1.0,maximum-scale=1.0,user-scalable=0"/>
    <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
    <meta http-equiv="Content-Type" content="text/html;"/>
    <meta name="renderer" content="webkit">
    <!--解决页面加载网络图片的403问题 , 可以让img标签预加载网络图片-->
    <meta name="referrer" content="no-referrer" />

    <!--markdown S -->
    <link rel="stylesheet" type="text/css" href="../../static/css/export_setting_css.css"/>
    <link rel="stylesheet" type="text/css" href="../../static/css/markdown_preview_css.css"/>
    <!--markdown E -->

    <!--left bar tools css E-->
    <link rel="stylesheet" type="text/css" href="../../static/css/nav.css">
    <link rel="stylesheet" type="text/css" href="http://www.jq22.com/jquery/font-awesome.4.6.0.css">
    <!--left bar tools css E-->

    <!-- 滚动条 S -->
    <link rel="stylesheet" href="../../static/css/optiscroll.css">
    <!-- 滚动条 E -->
</head>
<body>
<div id="m-wrapper" class="m-wrapper optiscroll">

    <!-- top 锚点 -->
    <a id="top"></a>

    <!-- left bar tools S -->
    <div class="leftNav-item">
        <ul>
            <li>
                <i class="fa fa-location-arrow"></i>
                <a href="http://jiongmefeishi.gitee.io/monster-blog/" target="_blank" class="rota">个人网站</a>
            </li>

            <li title="Git">
                <i class="fa fa-code-fork" aria-hidden="true"></i>
                <a href="http://jiongmefeishi.gitee.io/monster-blog/" target="_blank" class="rota">Git Page</a>
            </li>

            <li lay-data="img" data-fooc="http://resource.zqtaotao.cn/wx/qrcode/jiongmefeishi.jpg">
                <i class="fa fa-comments-o"></i><a href="javascript:;" class="rota">公众号</a>
            </li>

            <li lay-data="img" data-fooc="http://resource.zqtaotao.cn/weixincode.png">
                <i class="fa fa-money"></i><a href="javascript:;" class="rota">小额打赏</a>
            </li>
            <li title="返回顶部" class="for-top">
                <i class="fa fa-arrow-up"></i>
                <a href="#top" class="rota">去顶部</a>
            </li>
        </ul>
    </div>
    <!-- left bar tools E -->


    <!--content S-->


    <div id="export_content"><div class="output_wrapper" id="output_wrapper_id"><h3 id="hubuntu2004mysql80"><span>Ubuntu20.04安装mysql8.0并设置远程连接</span></h3><h1 hidden id="hubuntu2004mysql80"><span>Ubuntu20.04安装mysql8.0并设置远程连接</span></h1>
        <p>更新源</p>
        <pre><code class="hljs nginx"><span class="hljs-attribute">sudo</span>&nbsp;apt&nbsp;update<br></code></pre>
        <p>执行MySQL安装命令，安装完毕默认启动</p>
        <pre><code class="hljs sql">sudo&nbsp;apt&nbsp;<span class="hljs-keyword">install</span>&nbsp;mysql-<span class="hljs-keyword">server</span><br></code></pre>
        <p>查看/启动/停止  MySQL服务状态：</p>
        <pre><code class="hljs sql">sudo&nbsp;systemctl&nbsp;status&nbsp;mysql<br>sudo&nbsp;systemctl&nbsp;<span class="hljs-keyword">start</span>&nbsp;mysql<br>sudo&nbsp;systemctl&nbsp;<span class="hljs-keyword">stop</span>&nbsp;mysql<br></code></pre>
        <p>MySQL快速安装脚本<code>mysql_secure_installation</code>，。</p>
        <p>调用脚本来轻松设置和管理mysql 初始化工作,配置数据库服务器的安全性工作。</p>
        <pre><code class="hljs nginx"><span class="hljs-attribute">sudo</span>&nbsp;mysql_secure_installation<br></code></pre>
        <p>安全设置之一，选择MySQL设置密码的强度</p>
        <p>密码验证策略分为三个级别：低，中和强</p>
        <pre><code class="hljs vbnet">Securing&nbsp;the&nbsp;MySQL&nbsp;server&nbsp;deployment.<br><br>Connecting&nbsp;<span class="hljs-keyword">to</span>&nbsp;MySQL&nbsp;<span class="hljs-keyword">using</span>&nbsp;a&nbsp;blank&nbsp;password.<br><br>VALIDATE&nbsp;PASSWORD&nbsp;COMPONENT&nbsp;can&nbsp;be&nbsp;used&nbsp;<span class="hljs-keyword">to</span>&nbsp;test&nbsp;passwords<br><span class="hljs-keyword">and</span>&nbsp;improve&nbsp;security.&nbsp;It&nbsp;checks&nbsp;the&nbsp;strength&nbsp;<span class="hljs-keyword">of</span>&nbsp;password<br><span class="hljs-keyword">and</span>&nbsp;allows&nbsp;the&nbsp;users&nbsp;<span class="hljs-keyword">to</span>&nbsp;<span class="hljs-keyword">set</span>&nbsp;only&nbsp;those&nbsp;passwords&nbsp;which&nbsp;are<br>secure&nbsp;enough.&nbsp;Would&nbsp;you&nbsp;<span class="hljs-keyword">like</span>&nbsp;<span class="hljs-keyword">to</span>&nbsp;setup&nbsp;VALIDATE&nbsp;PASSWORD&nbsp;component?<br><br>Press&nbsp;y|Y&nbsp;<span class="hljs-keyword">for</span>&nbsp;Yes,&nbsp;any&nbsp;other&nbsp;<span class="hljs-keyword">key</span>&nbsp;<span class="hljs-keyword">for</span>&nbsp;No:&nbsp;y<br></code></pre>
        <p>安全设置之一，设置MySQL root用户的密码</p>
        <pre><code class="hljs sql">Please&nbsp;<span class="hljs-keyword">set</span>&nbsp;the&nbsp;<span class="hljs-keyword">password</span>&nbsp;<span class="hljs-keyword">for</span>&nbsp;root&nbsp;here.<br><br><span class="hljs-keyword">New</span>&nbsp;<span class="hljs-keyword">password</span>:&nbsp;<br><br>Re-enter&nbsp;<span class="hljs-keyword">new</span>&nbsp;<span class="hljs-keyword">password</span>:&nbsp;<br></code></pre>
        <p>接下来，脚本将要求删除匿名用户，限制root用户对本地计算机的访问，删除测试数据库并重新加载特权表。</p>
        <p>先以root用户身份登录到MySQL服务器，请输入：</p>
        <pre><code class="hljs nginx"><span class="hljs-attribute">sudo</span>&nbsp;mysql<br></code></pre>
        <p>设置外部程序（例如IDEA）以root用户身份登录到MySQL服务器</p>
        <pre><code class="hljs sql"><span class="hljs-keyword">ALTER</span>&nbsp;<span class="hljs-keyword">USER</span>&nbsp;<span class="hljs-string">'root'</span>@<span class="hljs-string">'localhost'</span>&nbsp;<span class="hljs-keyword">IDENTIFIED</span>&nbsp;<span class="hljs-keyword">WITH</span>&nbsp;mysql_native_password&nbsp;<span class="hljs-keyword">BY</span>&nbsp;<span class="hljs-string">'密码'</span>;<br><span class="hljs-comment">#&nbsp;刷新设置</span><br><span class="hljs-keyword">FLUSH</span>&nbsp;<span class="hljs-keyword">PRIVILEGES</span>;<br></code></pre>
        <p>之后便可以通过密码登录 mysql</p>
        <pre><code class="hljs nginx"><span class="hljs-attribute">mysql</span>&nbsp;-u&nbsp;root&nbsp;-p<br></code></pre>
        <h3 id="h"><span>远程访问相关设置</span></h3>
        <p><strong>向MySQL用户帐户授予权限</strong></p>
        <p>要授予对特定数据库用户帐户的所有特权，请使用以下命令：</p>
        <pre><code class="hljs sql"><span class="hljs-keyword">GRANT</span>&nbsp;ALL&nbsp;<span class="hljs-keyword">PRIVILEGES</span>&nbsp;<span class="hljs-keyword">ON</span>&nbsp;database_name.*&nbsp;<span class="hljs-keyword">TO</span>&nbsp;<span class="hljs-string">'database_user'</span>@<span class="hljs-string">'localhost'</span>;<br><br><span class="hljs-comment">#&nbsp;myroot&nbsp;用户设置所有主机可以访问,并给定操作数据库所有权限</span><br><br><span class="hljs-keyword">create</span>&nbsp;<span class="hljs-keyword">user</span>&nbsp;myroot@<span class="hljs-string">'%'</span>&nbsp;<span class="hljs-keyword">identified</span>&nbsp;<span class="hljs-keyword">by</span>&nbsp;<span class="hljs-string">'Mypasswd@123'</span>;<br><br><span class="hljs-keyword">grant</span>&nbsp;all&nbsp;<span class="hljs-keyword">privileges</span>&nbsp;<span class="hljs-keyword">on</span>&nbsp;*.*&nbsp;<span class="hljs-keyword">to</span>&nbsp;myroot@<span class="hljs-string">'%'</span>&nbsp;<span class="hljs-keyword">with</span>&nbsp;<span class="hljs-keyword">grant</span>&nbsp;<span class="hljs-keyword">option</span>;<br></code></pre>
        <p>要授予对所有数据库用户帐户的所有特权，请使用以下命令：</p>
        <pre><code class="hljs sql"><span class="hljs-keyword">GRANT</span>&nbsp;ALL&nbsp;<span class="hljs-keyword">PRIVILEGES</span>&nbsp;<span class="hljs-keyword">ON</span>&nbsp;*.*&nbsp;<span class="hljs-keyword">TO</span>&nbsp;<span class="hljs-string">'database_user'</span>@<span class="hljs-string">'localhost'</span>;<br></code></pre>
        <p>要对数据库中的特定表授予用户帐户的所有特权，请使用以下命令：</p>
        <pre><code class="hljs sql"><span class="hljs-keyword">GRANT</span>&nbsp;ALL&nbsp;<span class="hljs-keyword">PRIVILEGES</span>&nbsp;<span class="hljs-keyword">ON</span>&nbsp;database_name.table_name&nbsp;<span class="hljs-keyword">TO</span>&nbsp;<span class="hljs-string">'database_user'</span>@<span class="hljs-string">'localhost'</span>;<br></code></pre>
        <p>如果要仅授予特定数据库类型的用户帐户特定特权，请执行以下操作：</p>
        <pre><code class="hljs sql"><span class="hljs-keyword">GRANT</span>&nbsp;<span class="hljs-keyword">SELECT</span>,&nbsp;<span class="hljs-keyword">INSERT</span>,&nbsp;<span class="hljs-keyword">DELETE</span>&nbsp;<span class="hljs-keyword">ON</span>&nbsp;database_name.*&nbsp;<span class="hljs-keyword">TO</span>&nbsp;database_user@<span class="hljs-string">'localhost'</span>;<br></code></pre>
        <p><strong>从MySQL用户帐户撤消权限</strong></p>
        <p>如果您需要撤消一个用户帐户的一个或多个特权或所有特权，则语法几乎与授予它相同。例如，如果要撤消特定数据库上用户帐户的所有特权，请使用以下命令：</p>
        <pre><code class="hljs sql"><span class="hljs-keyword">REVOKE</span>&nbsp;ALL&nbsp;<span class="hljs-keyword">PRIVILEGES</span>&nbsp;<span class="hljs-keyword">ON</span>&nbsp;database_name.*&nbsp;<span class="hljs-keyword">TO</span>&nbsp;<span class="hljs-string">'database_user'</span>@<span class="hljs-string">'localhost'</span>;<br></code></pre>
        <p>刷新配置</p>
        <pre><code class="hljs sql"><span class="hljs-keyword">FLUSH</span>&nbsp;<span class="hljs-keyword">PRIVILEGES</span>;<br></code></pre>
        <p><strong>更改mysql配置文件：</strong></p>
        <p>在更改MySQL配置文件时一定要<strong>停止MySQL服务</strong>否则配置文件无法保存</p>
        <pre><code class="hljs nginx"><span class="hljs-attribute">sudo</span>&nbsp;systemctl&nbsp;stop&nbsp;mysql<br><br>vim&nbsp;/etc/mysql/mysql.conf.d/mysqld.cnf<br></code></pre>
        <p>注掉 bind-address = 127.0.0.1</p>
        <pre><code class="hljs shell"><span class="hljs-meta">#</span><span class="bash">&nbsp;<span class="hljs-built_in">bind</span>-address&nbsp;=&nbsp;127.0.0.1</span><br></code></pre>
        <p>配置字符集，统一字符集为utf8</p>
        <pre><code class="hljs cs">[<span class="hljs-meta">client</span>]中添加<br><span class="hljs-keyword">default</span>-character-<span class="hljs-keyword">set</span>=utf8<br>[<span class="hljs-meta">mysql</span>]中添加<br><span class="hljs-keyword">default</span>-character-<span class="hljs-keyword">set</span>=utf8<br>[<span class="hljs-meta">mysqld</span>]中添加<br>character-<span class="hljs-keyword">set</span>-server&nbsp;=&nbsp;utf8<br></code></pre>
        <p>设置后重启mysql 服务，查询字符集设置结果</p>
        <pre><code class="hljs sql"><span class="hljs-keyword">show</span>&nbsp;<span class="hljs-keyword">variables</span>&nbsp;<span class="hljs-keyword">like</span>&nbsp;<span class="hljs-string">'%char%'</span>;<br></code></pre>
        <p>设置成功结果显示</p>
        <pre><code class="hljs ruby">+--------------------------------------+----------------------------+<br><span class="hljs-params">|&nbsp;Variable_name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|</span>&nbsp;Value&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-params">|<br>+--------------------------------------+----------------------------+<br>|</span>&nbsp;character_set_client&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-params">|&nbsp;utf8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|</span><br><span class="hljs-params">|&nbsp;character_set_connection&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|</span>&nbsp;utf8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-params">|<br>|</span>&nbsp;character_set_database&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-params">|&nbsp;utf8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|</span><br><span class="hljs-params">|&nbsp;character_set_filesystem&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|</span>&nbsp;binary&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-params">|<br>|</span>&nbsp;character_set_results&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-params">|&nbsp;utf8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|</span><br><span class="hljs-params">|&nbsp;character_set_server&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|</span>&nbsp;utf8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-params">|<br>|</span>&nbsp;character_set_system&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-params">|&nbsp;utf8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|</span><br><span class="hljs-params">|&nbsp;character_sets_dir&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|</span>&nbsp;/usr/share/mysql/charsets/&nbsp;<span class="hljs-params">|<br>|</span>&nbsp;validate_password.special_char_count&nbsp;<span class="hljs-params">|&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|</span><br>+--------------------------------------+----------------------------+<br><span class="hljs-number">9</span>&nbsp;rows&nbsp;<span class="hljs-keyword">in</span>&nbsp;set&nbsp;(<span class="hljs-number">0</span>.<span class="hljs-number">01</span>&nbsp;sec)<br></code></pre>
        <p>启动MySQL服务：</p>
        <pre><code class="hljs nginx"><span class="hljs-attribute">sudo</span>&nbsp;service&nbsp;mysql&nbsp;start<br></code></pre>
        <p>查看服务状态：</p>
        <pre><code class="hljs nginx"><span class="hljs-attribute">sudo</span>&nbsp;service&nbsp;mysql&nbsp;status<br></code></pre>
        <p>远程登录测试</p>
        <p>使用 Navicat Premium 12 测试远程登录</p>
        <p>1、新建MySQL连接</p>
        <p>2、配置连接信息</p>
        <p>3、点击测试连接   ---- &gt; 显示连接成功</p>
        <p>注意：如果连接测试失败，远程Navicat无法连接，提示密码规则错误，需要更改mysql密码规则</p>
        <p>mysql8的加密方式和Navicat不一样，使用和Navicat匹配的密码加密规则，重新设定密码。</p>
        <pre><code class="hljs sql"><span class="hljs-keyword">ALTER</span>&nbsp;<span class="hljs-keyword">USER</span>&nbsp;<span class="hljs-string">'用户'</span>@<span class="hljs-string">'localhost'</span>&nbsp;<span class="hljs-keyword">IDENTIFIED</span>&nbsp;<span class="hljs-keyword">BY</span>&nbsp;<span class="hljs-string">'密码'</span>&nbsp;<span class="hljs-keyword">PASSWORD</span>&nbsp;<span class="hljs-keyword">EXPIRE</span>&nbsp;<span class="hljs-keyword">NEVER</span>;<br><span class="hljs-keyword">ALTER</span>&nbsp;<span class="hljs-keyword">USER</span>&nbsp;<span class="hljs-string">'用户'</span>@<span class="hljs-string">'localhost'</span>&nbsp;<span class="hljs-keyword">IDENTIFIED</span>&nbsp;<span class="hljs-keyword">WITH</span>&nbsp;mysql_native_password&nbsp;<span class="hljs-keyword">BY</span>&nbsp;<span class="hljs-string">'密码'</span>;<br></code></pre></div></div>


    <!--content E-->
</div>

</body>
<!--left bar tools js S-->
<script src="http://www.jq22.com/jquery/jquery-1.10.2.js"></script>
<script type="text/javascript" src="../../static/js/nav.js"></script>
<!--left bar tools js E-->

<!-- 滚动条 S -->
<script type="text/javascript" src="../../static/js/optiscroll.js"></script>
<!-- 滚动条 E -->

<!--commonjs S-->
<script type="text/javascript" src="../../static/js/common.js"></script>
<!--commonjs E-->
</html>
